package com.cmcc.zysoft.infocollection.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import com.cmcc.zysoft.infocollection.model.Headship;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;
import com.starit.common.dao.support.Pagination;

/**
 * @author 喻金龙
 * <br />邮箱: yu.jinlong@ustcinfo.com
 * <br />描述: HeadshipPcDao.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-9-7 下午2:33:01
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

@Repository
public class HeadshipPcDao extends HibernateBaseDaoImpl<Headship, String> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取所有岗位信息.
	 * @param page
	 * @param rows
	 * @param companyId 企业ID
	 * @param keyword 搜索关键字
	 * @return Map<String, Object>
	 */
	public Map<String, Object> getAllHeadship(int page, int rows, String companyId, String keyword) {
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		String rowSql = 
				" SELECT a.headship_id AS headshipId, a.headship_name AS headshipName, a.description AS description, " +
				" a.status AS 'status', a.company_id AS companyId, a.create_time AS createTime, a.modify_time AS modifyTime, " +
				" (SELECT b.real_name FROM tb_c_system_user b WHERE a.create_man = b.user_id) AS createMan, " +
				" (SELECT c.real_name FROM tb_c_system_user c WHERE a.modify_man = c.user_id) AS modifyMan, com.company_name AS companyName, " +
				" (SELECT COUNT(*) FROM tb_c_employee e WHERE e.del_flag = '0' AND e.headship_id = a.headship_id) AS empNum " +
				" FROM tb_c_headship a, tb_c_company com " +
				" WHERE a.del_flag = '0' AND a.company_id = com.company_id ";
		String countSql = 
				" SELECT COUNT(*) FROM tb_c_headship a, tb_c_company com " +
				" WHERE a.del_flag='0' AND a.company_id = com.company_id ";
		if(StringUtils.hasText(keyword)) {
			rowSql += " AND a.headship_name LIKE '%" + keyword + "%' ";
			countSql += " AND a.headship_name LIKE '%" + keyword + "%' ";
		} else {
			if(StringUtils.hasText(companyId)) {
				rowSql += " AND a.company_id = '" + companyId + "' ";
				countSql += " AND a.company_id = '" + companyId + "' ";
			}
		}
		rowSql += " ORDER BY com.company_id LIMIT ?, ? ";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", list);
		map.put("total", total);
		return map;
	}
	
	/**
	 * 判断岗位名是否同名
	 * @param headshipName
	 * @return true表示同名   false表示不同名
	 */
	public boolean isHeadshipNameEqual(String companyId , String headshipName,String headshipId){
		String checkSql = "select h.* from tb_c_headship h where h.del_flag='0' and h.headship_name = '"+ headshipName +"' and h.company_id = '"+companyId+ "'";
		if("0".equals(headshipId)==false){
			checkSql += " AND h.headship_id != '" + headshipId + "'";
		}
		List<Map<String, Object>> list=null;
		list=this.jdbcTemplate.queryForList(checkSql);
		int listSize=list.size();
		if(listSize==0){return false;}
		else{ return true;}		
	}
	
	/**
	 * 添加岗位信息
	 * 
	 * @param headsh 岗位对象
	 */
	public void saveHeadship(Headship headship) {
		this.getHibernateTemplate().save(headship);
	}
	
	/**
	 * 根据岗位id获取岗位信息
	 * 
	 * @param headshipId 岗位id
	 * @return
	 */
	public Headship getHeadship(String headshipId) {
		Headship headship = this.get(headshipId);
		return headship;
	}
	
	/**
	 * 修改岗位信息
	 * 
	 * @param headship 岗位对象
	 */
	public void updateHeadship(Headship headship) {
		this.getHibernateTemplate().merge(headship);
	}
	
	/**
	 * 删除岗位信息
	 * 
	 * @param headshipId
	 */
	public void deleteHeadship(String headshipId) {
		String sql = " delete from tb_c_headship where headship_id = '" + headshipId + "'";
		jdbcTemplate.execute(sql);
	}
	
	/**
	 * 根据查询条件，获取岗位信息列表
	 * 
	 * @param headshipName 岗位名称
	 * @param page
	 * @param rows
	 * @return
	 */
	public Pagination<Object> getHeadshipByCondition(String headshipName, int page, int rows,String companyId) {
		String rowSql = "select new Map(h.headshipId as headshipId,comp.companyName as companyName ,h.headshipName as headshipName, "+
	            " h.createTime as createTime ,h.createMan as createMan , h.modifyTime as modifyTime , h.modifyMan as modifyMan ," +
				" h.description as description,h.status as status) from Headship h ,Company comp  where comp.companyId=h.companyId";
		String countSql = "select count(*) from Headship h ,Company comp where comp.companyId=h.companyId ";
			if(StringUtils.hasText(headshipName.trim())){
				rowSql += " and h.delFlag='0' and h.headshipName like '%"+ headshipName.trim() +"%' and h.companyId = '"+companyId+"' order by h.headshipId desc ";
				countSql += " and h.delFlag='0' and  h.headshipName like '%"+ headshipName.trim() +"%' and h.companyId = '"+companyId+"' order by h.headshipId desc ";
			}else{
				rowSql += " and h.delFlag='0' and h.companyId = '"+companyId+"' order by h.headshipId desc ";
				countSql += " and h.delFlag='0' and h.companyId = '"+companyId+"' order by h.headshipId desc ";
			}				
		int offset = (page-1)*rows;
		return this.findPageByHQL(rowSql, countSql, offset, rows);
	}
	
	/**
	 * 根据岗位名称获取岗位信息.
	 * @param companyId 企业ID
	 * @param headshipName 岗位名称
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> getHeadshipId(String companyId, String headshipName) {
		String sql = " SELECT a.* FROM tb_c_headship a WHERE a.del_flag = '0' AND a.company_id = ? AND a.headship_name = ? ";
		return this.jdbcTemplate.queryForList(sql, companyId, headshipName);
	}
}